package com.liuqh.tag.poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * LL 2023年5月1日 上午11:35:54
 */

public class CreateExcelTest {
	public static void main(String[] args) {
		OutputStream out = null;
		List<Map<String, Object>> list = Lists.newArrayList();
		Map<String, Object> map = new HashMap<String, Object>();
		list.add(map);
		map.put("0", 0);
		map.put("1", 1);
		map.put("2", 2);
		map.put("3", 3);
		Map<String, Object> map2 = new HashMap<String, Object>();
		list.add(map2);
		map2.put("0", 0);
		map2.put("1", 1);
		map2.put("2", 2);
		map2.put("3", 3);
		Map<String, Object> map3 = new HashMap<String, Object>();
		list.add(map3);
		map3.put("0", 0);
		map3.put("1", 1);
		map3.put("2", 2);
		map3.put("3", 3);

		String sheetName = "xxx";

		XSSFWorkbook wb = new XSSFWorkbook();
		FileOutputStream fileOut = null;
		try {
			XSSFSheet sheet = wb.createSheet(sheetName);
			/**
			 * 列名
			 */
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			cell.setCellValue("日期");
			cell = row.createCell(1);
			cell.setCellValue("柱状1");
			cell = row.createCell(2);
			cell.setCellValue("柱状2");
			cell = row.createCell(3);
			cell.setCellValue("柱状3");
			/**
			 * 数据行
			 */
			for (int j = 0; j < list.size(); j++) {
				row = sheet.createRow(j + 1);
				cell = row.createCell(j);
				cell.setCellValue(Integer.valueOf(list.get(j).get("0").toString()));
				cell = row.createCell(j + 1);
				cell.setCellValue(Integer.valueOf(list.get(j).get("1").toString()));
				cell = row.createCell(j + 2);
				cell.setCellValue(Integer.valueOf(list.get(j).get("2").toString()));
				cell = row.createCell(j + 3);
				cell.setCellValue(Integer.valueOf(list.get(j).get("3").toString()));
			}

			int rowsPic = list.size();
			// 创建一个画布
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			// 前四个默认0，[0,5]：从0列list.size() + 1行开始;[7,26]:到list.size() + 5列rowsPic + 16行结束
			// 默认宽度(14-8)*12
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, rowsPic + 1, rowsPic + 5, rowsPic + 16);
			// 创建一个chart对象
			XSSFChart chart = drawing.createChart(anchor);
			// 标题
			chart.setTitleText(sheetName);
			// 标题覆盖
			chart.setTitleOverlay(false);

			// 图例位置
			XDDFChartLegend legend = chart.getOrAddLegend();
			legend.setPosition(LegendPosition.TOP);

			// 分类轴标(X轴),标题位置
			XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
			bottomAxis.setTitle("日期");
			// 值(Y轴)轴,标题位置
			XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
			leftAxis.setTitle("xxx");
			// CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
			// 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 4]
			XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
					new CellRangeAddress(1, rowsPic, 0, 0));
			// 数据1，单元格范围位置[1, 0]到[1, 4]
			XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
					new CellRangeAddress(1, rowsPic, 1, 1));
			// 数据2，单元格范围位置[2, 0]到[2, 4]
			XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
					new CellRangeAddress(1, rowsPic, 2, 2));
			// 数据3，单元格范围位置[3, 0]到[3, 4]
			XDDFNumericalDataSource<Double> population2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
					new CellRangeAddress(1, rowsPic, 3, 3));
			// bar：条形图，
			XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
			leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
			// 设置为可变颜色
			bar.setVaryColors(true);
			// 条形图方向，纵向/横向：纵向
			bar.setBarDirection(BarDirection.COL);
			// 图表加载数据，条形图1
			XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
			// 条形图例标题
			series1.setTitle(" ", null);
			XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.WHITE));
			// 条形图，填充颜色
			series1.setFillProperties(fill);

			// 图表加载数据，条形图2
			XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) bar.addSeries(countries, population);
			// 条形图例标题
			series2.setTitle(" ", null);
			XDDFSolidFillProperties fill2 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.WHITE));
			// 条形图，填充颜色
			series2.setFillProperties(fill2);
			// 图表加载数据，条形图2
			XDDFBarChartData.Series series3 = (XDDFBarChartData.Series) bar.addSeries(countries, population2);
			// 条形图例标题
			series3.setTitle(" ", null);
			XDDFSolidFillProperties fill3 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.WHITE));
			// 条形图，填充颜色
			series3.setFillProperties(fill3);
			// 绘制
			chart.plot(bar);

			// 右Y轴
			XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);

			rightAxis.setTitle("yyy");
			// 右Y轴和X轴交叉点在X轴最大值位置
			rightAxis.setCrosses(AxisCrosses.MAX);
			// 构建坐标轴
			rightAxis.crossAxis(bottomAxis);
			bottomAxis.crossAxis(rightAxis);
			XDDFBarChartData barR = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, rightAxis);
			rightAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
			barR.setVaryColors(true);
			// 条形图方向，纵向/横向：纵向
			barR.setBarDirection(BarDirection.COL);
			// 图表加载数据，条形图1
			XDDFBarChartData.Series seriesR1 = (XDDFBarChartData.Series) barR.addSeries(countries, area);
			// 条形图例标题
			seriesR1.setTitle("柱状1", null);
			XDDFSolidFillProperties fillR = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
			// 条形图，填充颜色
			seriesR1.setFillProperties(fillR);
			// 图表加载数据，条形图2
			XDDFBarChartData.Series seriesR2 = (XDDFBarChartData.Series) barR.addSeries(countries, population);
			// 条形图例标题
			seriesR2.setTitle("柱状2", null);
			XDDFSolidFillProperties fillR2 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
			// 条形图，填充颜色
			seriesR2.setFillProperties(fillR2);
			// 图表加载数据，条形图2
			XDDFBarChartData.Series seriesR3 = (XDDFBarChartData.Series) barR.addSeries(countries, population2);
			// 条形图例标题
			seriesR3.setTitle("柱状3", null);
			XDDFSolidFillProperties fillR3 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.YELLOW));
			// 条形图，填充颜色
			seriesR3.setFillProperties(fillR3);
			// 绘制
			chart.plot(barR);
			// 打印图表的xml
			System.out.println(chart.getCTChart());

			// 将输出写入excel文件
			String filename = "d:\\word\\表.xlsx";
			fileOut = new FileOutputStream(filename);

			/**
			 * 将数据流输出给前端
			 */
			wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if (out != null) {
				try {
					out.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
			if (fileOut != null) {
				try {
					fileOut.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
